package dataBase;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

import delegaciones.Delegacion;
import empleados.Empleado;

import auxiliar.Provincias;

public class JDBCDelegacionDAO {
	
	private Connection conn;
	
	
	public JDBCDelegacionDAO(){
		conn = ConnectionManager.getInstance().checkOut();
	}
	
	public void finalice(){
		ConnectionManager.getInstance().checkIn(conn);
	}
	
	//Metodo que devuelve todas las provincias disponibles
	public List<Provincias> selectTodasProvincias() {
		
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
        ResultSet result = null;
        
        List<Provincias> lp = new LinkedList<Provincias>();
        
        String sql = "SELECT * FROM provincias ORDER BY provincia";
    
        try {
            stmt = conn.prepareStatement(sql);
            result = stmt.executeQuery();

            while (result.next()) {
                Provincias p = new Provincias();
                
            	p.setNombre(result.getString("provincia"));
                
                lp.add(p);
            }
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
        return lp;
	}
	
	//crea una delegacion nueva
	public void insertDelegacion(Delegacion d) {
		Connection conn = ConnectionManager.getInstance().checkOut();

		String sql = "INSERT INTO delegaciones (provincia_delegacion,Cif_empresa,direccion,telefono) VALUES (?, ?, ?, ?) ";
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement(sql);

            stmt.setString(1, d.getprovincia());
            stmt.setString(2, d.getCIF_Empresa());
            stmt.setString(3, d.getDireccion());
            stmt.setString(4, d.getTelefono());

            stmt.executeUpdate();

        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
	}
	
	//Devuelve los datos de una delegacion
	public Delegacion selectDelegacion(String provincia, String Cif_empresa) {
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
        ResultSet result = null;
        
        Delegacion d = null;
       
        String sql = "SELECT * FROM delegaciones WHERE (provincia_delegacion = ? AND Cif_empresa = ?) ";
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, provincia);
            stmt.setString(2, Cif_empresa);
            result = stmt.executeQuery();

            if(result.next()){
            	d = new Delegacion();
            	d.setprovincia(result.getString("provincia_delegacion"));
            	d.setDireccion(result.getString("direccion"));
            	d.setTelefono(result.getString("telefono"));
            	d.setCIF_Empresa(result.getString("Cif_empresa"));

            }
        } catch (SQLException u) {
            System.out.println("Message: " + u.getMessage());
            System.out.println("SQLState: " + u.getSQLState());
            System.out.println("ErrorCode: " + u.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException u) {
            }
        }
        return d;
	}
	
	//modifica los datos de una empresa
	public void updateDelegacion(Delegacion d){
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		ResultSet result = null;
		String sql = "UPDATE delegaciones SET direccion = ?,telefono = ?" +
				"WHERE (provincia_delegacion = ? AND Cif_empresa = ?)";
		
		try{
			stmt = conn.prepareStatement(sql);
			//con "stmt.set" estas cambiando los datos de la BD, no del objeto
			//stmt.setString(1, e.getDNI());
			stmt.setString(1, d.getDireccion());
			stmt.setString(2, d.getTelefono());
			
			stmt.setString(3, d.getprovincia());
			stmt.setString(4, d.getCIF_Empresa());
			
			stmt.executeUpdate();
		}catch(SQLException ex){
			System.out.println("Message: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("ErrorCode: " + ex.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException ex) {
            }
        }
	}
	
	//borrar una delegacion
	public void deleteDelegacion(String provincia, String cif){
		
		String sql = "DELETE FROM delegaciones WHERE (provincia_delegacion = ? AND Cif_empresa = ?) ";
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement(sql);
            
            stmt.setString(1, provincia);
            stmt.setString(2, cif);
            
            stmt.executeUpdate();
        } catch (SQLException e) {
            System.err.println("Message: " + e.getMessage());
            System.err.println("SQLState: " + e.getSQLState());
            System.err.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }
        }
	}
	
	//inserta en la tabla empleados_delegaciones
	public void insertEmpleados_Delegaciones(Empleado e,Delegacion d) {
		Connection conn = ConnectionManager.getInstance().checkOut();

		String sql = "INSERT INTO empleados_delegaciones (dni,cif_empresa,provincia) VALUES (?, ?, ?) ";
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement(sql);

            stmt.setString(1, e.getDNI());
            stmt.setString(2, d.getCIF_Empresa());
            stmt.setString(3, d.getprovincia());

            stmt.executeUpdate();

        } catch (SQLException ex) {
            System.out.println("Message: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("ErrorCode: " + ex.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException ex) {
            }
        }
	}
	
	//muestra una lista de todos los empleados que trabajan para una delegacion concreta
	public List<Empleado> selectTodosEmpleados(Delegacion d){
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
        ResultSet result = null;
        
        List<Empleado> le = new LinkedList<Empleado>();
        
        String sql = "SELECT * FROM empleados_delegaciones WHERE (cif_empresa = ? AND provincia = ?) ORDER BY dni";
    
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, d.getCIF_Empresa());
            stmt.setString(2, d.getprovincia());
            result = stmt.executeQuery();

            while (result.next()) {
                Empleado e = new Empleado();
                
            	e.setDNI(result.getString("dni"));
                
                le.add(e);
            }
        } catch (SQLException ex) {
            System.out.println("Message: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("ErrorCode: " + ex.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException ex) {
            }
        }
        return le;
	}
	
	//pasando un dni de empleado obtener una delegacion (tabla empleados_delegaciones)
	public Delegacion selectDelegacionDeEmpleado(String dni){
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
        ResultSet result = null;
        
        Delegacion d = null;
       
        String sql = "SELECT * FROM empleados_delegaciones WHERE (dni = ?) ";
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, dni);
            result = stmt.executeQuery();
            
            if(result.next()){
            	//d = this.selectDelegacion(result.getString("cif_empresa"),result.getString("provincia"));
            	d = new Delegacion();
            	d.setCIF_Empresa(result.getString("cif_empresa"));
            	d.setprovincia(result.getString("provincia"));
            }
            
        } catch (SQLException u) {
            System.out.println("Message: " + u.getMessage());
            System.out.println("SQLState: " + u.getSQLState());
            System.out.println("ErrorCode: " + u.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException u) {
            }
        }
        return d;
	}
	
	//modifica los datos de la tabla "empleados_delegaciones"
	public void updateDelegacionDeEmpleado(Empleado e, Delegacion d){
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		ResultSet result = null;
		String sql = "UPDATE empleados_delegaciones SET cif_empresa = ?,provincia = ?" +
				"WHERE (dni = ?)";
		
		try{
			stmt = conn.prepareStatement(sql);
			//con "stmt.set" estas cambiando los datos de la BD, no del objeto
			//stmt.setString(1, e.getDNI());
			stmt.setString(1, d.getCIF_Empresa());
			stmt.setString(2, d.getprovincia());
			
			stmt.setString(3, e.getDNI());
			
			stmt.executeUpdate();
		}catch(SQLException ex){
			System.out.println("Message: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("ErrorCode: " + ex.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException ex) {
            }
        }
	}
}
